﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace BookStore.Data
{
    public class productController
    {
        static SqlConnection con = sqlDataProvider.getConnect();
        static DataTable dt;
        protected DataRow execReturnDataRow(string strSelect)
        {
            SqlDataAdapter da = new SqlDataAdapter(strSelect, con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt.Rows[0];
        }
            
        protected DataTable execReturnDataTable(string strSelect)
        {

            SqlDataAdapter da = new SqlDataAdapter(strSelect.ToString(),con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public productInfo getById(int id)
        {
            string strSelect = "select * from tblProduct where productID = " + id;
            DataRow dr = execReturnDataRow(strSelect);
            productInfo sp = new productInfo();
            sp.productID = Convert.ToInt32(dr[0]);
            sp.CatogoryID = Convert.ToInt32(dr[1]);
            sp.productName = dr[2].ToString();
            sp.ManufacturerID = Convert.ToInt32(dr[3]);
            sp.price = float.Parse(dr[4].ToString());
            sp.quantity = Convert.ToInt32(dr[5]);
            //sp.sale = Boolean.Parse(dr[6].ToString());
            // sp.active = Boolean.Parse(dr[7].ToString());
            sp.description = dr[6].ToString();
            //sp.authorID = Convert.ToInt32(dr[7]);
            sp.image = dr[7].ToString();
            sp.Productcode = dr[8].ToString();
            return sp;
        }

        public SqlDataReader ExcuteData(string Cautruyvan, CommandType Kieutruyvan, params SqlParameter[] parameter)
        {
           
            SqlCommand cmd = new SqlCommand(Cautruyvan, con);
            cmd.CommandType = Kieutruyvan;
            cmd.Parameters.Clear();
            for (int i = 0; i < parameter.Length - 1; i++)
            {
                cmd.Parameters.Add(parameter);
            }
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        //phuong thuc lay ve ten sach
        public  string ProductName(productInfo pro)
        {
            string _Tensach = "";
            SqlDataReader DR = ExcuteData("select productName from tblProduct where productID =" + pro.productID, CommandType.Text);
            DR.Read();
            _Tensach = DR[0].ToString();
            return _Tensach;
        }
        //phuong thuc lay ve gia tien
        public float Price(productInfo pro)
        {
            float _Giabia = 0;
            SqlDataReader DR = ExcuteData("SELECT price FROM tblProduct WHERE productID =" + pro.productID, CommandType.Text);
            DR.Read();
            _Giabia = float.Parse(DR[0].ToString());
            return _Giabia;
        }

        public DataTable getByIdReturnDataTable(int id)
        {
            string strSelect = "select * from tblProduct where productID = " + id;

            return execReturnDataTable(strSelect);
        }

        #region getbyID
        public static productInfo getbyID(int productID)
        {
            SqlCommand cmd = new SqlCommand("getIDProduct", con);
            productInfo objProduct = new productInfo();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@productID", productID));
            SqlDataReader dr = cmd.ExecuteReader(); ;
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    objProduct.productID = dr.GetInt32(0);
                    objProduct.CatogoryID = dr.GetInt32(1);
                    objProduct.productName = dr.GetString(2);
                    objProduct.manufacturerID = dr.GetInt32(3);
                    objProduct.price = float.Parse(dr.GetString(4));
                    objProduct.quantity = dr.GetInt32(5);
                    objProduct.sale = dr.GetBoolean(6);
                    objProduct.description = dr.GetString(7);
                    objProduct.authorID = dr.GetInt32(8);
                    objProduct.image = dr.GetString(9).ToString();

                }
            }
            return objProduct;
        }

        #endregion

        #region getbyAll
        public static DataTable ListProduct()
        {
            using (SqlDataAdapter da = new SqlDataAdapter("listProduct", con))
            {
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                dt = new DataTable();
                da.Fill(dt);
            }
            return dt;
        }
        #endregion

        #region insert
        public static bool insertProduct(productInfo objProduct)
        {
            using (SqlCommand cmd = new SqlCommand("insertProduct", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@catogoryID", objProduct.CatogoryID));

                //cmd.Parameters.Add(new SqlParameter("@productID", objProduct.productID));

                cmd.Parameters.Add(new SqlParameter("@productName", objProduct.productName));
                cmd.Parameters.Add(new SqlParameter("@manufacturerID", objProduct.manufacturerID));
                cmd.Parameters.Add(new SqlParameter("@price", objProduct.price));
                cmd.Parameters.Add(new SqlParameter("@quantity", objProduct.quantity));
                cmd.Parameters.Add(new SqlParameter("@sale", objProduct.sale));

                cmd.Parameters.Add(new SqlParameter("@dateadd",objProduct.Date));
                cmd.Parameters.Add(new SqlParameter("@active", objProduct.Active));

                cmd.Parameters.Add(new SqlParameter("@description", objProduct.description));
                cmd.Parameters.Add(new SqlParameter("@authorID", objProduct.authorID));
                cmd.Parameters.Add(new SqlParameter("@images", objProduct.image));
                cmd.ExecuteNonQuery();
            }
            return true;
        }
        #endregion

        #region update
        public static bool updateProduct(productInfo objProduct)
        {
            using (SqlCommand cmd = new SqlCommand("updateProduct", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@productID", objProduct.productID));
                cmd.Parameters.Add(new SqlParameter("@productName", objProduct.productName));
                cmd.Parameters.Add(new SqlParameter("@catogoryID", objProduct.CatogoryID));
                cmd.Parameters.Add(new SqlParameter("@manufacturerID", objProduct.manufacturerID));
                cmd.Parameters.Add(new SqlParameter("@price", objProduct.price));
                cmd.Parameters.Add(new SqlParameter("@quantity", objProduct.quantity));
                cmd.Parameters.Add(new SqlParameter("@sale", objProduct.sale));
                cmd.Parameters.Add(new SqlParameter("@description", objProduct.description));
                cmd.Parameters.Add(new SqlParameter("@authorID", objProduct.authorID));
                cmd.Parameters.Add(new SqlParameter("@images", objProduct.image));
                cmd.ExecuteNonQuery();
            }
            return true;
        }
        #endregion

        #region delete
        public static bool deleteProduct(int productID)
        {
            using (SqlCommand cmd = new SqlCommand("deleteProduct", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@productID", productID));
                cmd.ExecuteNonQuery();
            }
            return true;
        }
        #endregion

        #region checkProductID

        //public static bool checkProductID(int productID) {
        //    bool test = false;
        //    SqlCommand cmd = new SqlCommand("checkProductID",con);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    cmd.Parameters.Add(new SqlParameter("@productID", productID));
        //    SqlDataReader dr = cmd.ExecuteReader(); ;
        //    if (dr.HasRows)
        //    {
        //        test = true;
        //        con.Open();
        //    }
        //    dr.Close();
        //    return test;
        //}
        #endregion
        public static DataTable Get_ProductAsTable(string whereSql, string orderBy) 
        {
            using (SqlCommand cmd = new SqlCommand("[usp_Product_SelectGeneral]", con)) 
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@SqlWhere", whereSql));
                cmd.Parameters.Add(new SqlParameter("@SqlOrderBy", orderBy));
                SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                try
                {
                    DataTable tbl = new DataTable();
                    dataAdapter.Fill(tbl);
                    return tbl;
                }
                catch
                {
                    return null;
                }
                finally
                {
                    if (con.State == ConnectionState.Open)
                        con.Close();
                }
                
              
            }
         }

        //public static bool checkProductID(int productID)
        //{
        //    bool test = false;
        //    SqlCommand cmd = new SqlCommand("checkProductID", con);
        //    cmd.CommandType = CommandType.StoredProcedure;
        //    cmd.Parameters.Add(new SqlParameter("@productID", productID));
        //    SqlDataReader dr = cmd.ExecuteReader(); ;
        //    if (dr.HasRows)

        //    {
                
        //    }
        //}
        public static DataTable Get_productname(string name) 
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM tblProduct Where productName like'%" + name + "%'", con))
            {
                dt = new DataTable();
                adapter.Fill(dt);
                return dt; 
            }
        }
        public static DataTable Get_productnamewithcata(string cat,string name)
        {
            string sql = "SELECT dbo.tblProduct.productID, dbo.tblProduct.catogoryID, dbo.tblCatogory.catogryName, dbo.tblProduct.productName, dbo.tblProduct.manufacturerID, dbo.tblProduct.price,  dbo.tblProduct.quantity, dbo.tblProduct.sale, dbo.tblProduct.dateadd, dbo.tblProduct.active, dbo.tblProduct.description, dbo.tblProduct.authorID, dbo.tblProduct.images, dbo.tblProduct.productCode FROM  dbo.tblProduct INNER JOIN dbo.tblCatogory ON dbo.tblProduct.catogoryID = dbo.tblCatogory.catogoryID WHERE dbo.tblCatogory.catogryName=N'" + cat + "'  AND dbo.tblProduct.productName like'%" + name + "%'";
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, con))
            {
                //adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
               // DataSet ds = new DataSet();
                dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
        public static DataTable New_product() 
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter("[New_product_date]",con))
            {
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        
        }
        public static DataTable Top_productOLD()
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter("[Top_ProductOLD]", con))
            {
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }

        }
        public static DataTable Top_productBestsellers()
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter("[Top_ProductOLD]", con))
            {
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }

        }
        public static DataTable Get_ProductNameLike()
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter("[Get_ProductNameLike]", con))
            {
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }

        }
        public static DataTable Get_ProductVPP()
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter("[Getvpp]", con))
            {
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }

        }
        public static DataTable FindIDcuswithproduct(string email)
        {
            using (SqlCommand cmd = new SqlCommand("FindIDcuswithproduct",con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@email",email));
                SqlDataAdapter data = new SqlDataAdapter(cmd);
                dt = new DataTable();
                data.Fill(dt);
                return dt;
            }
        }
        
    }
}
